﻿using EPE.DBUtility;
using EPE.IDAL;
using EPE.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace EPE.MySqlDAL
{
    public class ShopsBuildImpl : IShopsBuild
    {
        public DataSet GetListByPage(string strProjectName, string strBuildName,string companyid, string orderby, int pageIndex, int dataCount)
        {
            string strSQL;

            DataSet retDS = new DataSet();

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " SELECT B.buildid,B.projectid,B.buildname,B.buildfuction,B.alllayer,B.groudlayer,B.startdate,B.enddate,B.located,B.property,B.area,B.buildarea,p.projectname ";
                strSQL = strSQL + " FROM tbl_ShopsBuild B,tbl_ShopsProject P ";
                strSQL = strSQL + " WHERE 1=1 ";
                strSQL = strSQL + " AND B.projectid=P.projectid ";
                strSQL = strSQL + " AND P.companyid=@companyid ";

                if (!string.IsNullOrEmpty(strProjectName))
                {
                    strSQL = strSQL + " AND projectname=@projectname ";
                }
                if (!string.IsNullOrEmpty(strBuildName))
                {
                    strSQL = strSQL + " AND buildname=@buildname ";
                }

                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("projectname", strProjectName);
                clsDbTool.ParameterAdd("buildname", strBuildName);
                clsDbTool.ParameterAdd("companyid", companyid);
                clsDbTool.CommandText = Pagination.PagingDataView(strSQL, pageIndex, dataCount, orderby);
                clsDbTool.Open();

                retDS = clsDbTool.GetDataSet();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
            return retDS;
        }
        public void Insert(CShopsBuild model)
        {
            string strSQL;

            int ret;

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " INSERT INTO tbl_shopsbuild (  ";
                strSQL = strSQL + "projectid ,";
                strSQL = strSQL + "buildname ,";
                strSQL = strSQL + "buildfuction ,";
                strSQL = strSQL + "alllayer ,";
                strSQL = strSQL + "groudlayer ,";
                strSQL = strSQL + "startdate ,";
                strSQL = strSQL + "enddate ,";
                strSQL = strSQL + "located ,";
                strSQL = strSQL + "property ,";
                strSQL = strSQL + "area ,";
                strSQL = strSQL + "buildarea)";

                strSQL = strSQL + "VALUES (@projectid ,";
                strSQL = strSQL + "@buildname ,";
                strSQL = strSQL + "@buildfuction ,";
                strSQL = strSQL + "@alllayer ,";
                strSQL = strSQL + "@groudlayer ,";
                strSQL = strSQL + "@startdate ,";
                strSQL = strSQL + "@enddate ,";
                strSQL = strSQL + "@located ,";
                strSQL = strSQL + "@property ,";
                strSQL = strSQL + "@area ,";
                strSQL = strSQL + "@buildarea)";




                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("projectid", model.projectid);
                clsDbTool.ParameterAdd("buildname", model.buildname);
                clsDbTool.ParameterAdd("buildfuction", model.buildfuction);
                clsDbTool.ParameterAdd("alllayer", model.alllayer);
                clsDbTool.ParameterAdd("groudlayer", model.groudlayer);
                clsDbTool.ParameterAdd("startdate", model.startdate);
                clsDbTool.ParameterAdd("enddate", model.enddate);
                clsDbTool.ParameterAdd("located", model.located);
                clsDbTool.ParameterAdd("property", model.property);
                clsDbTool.ParameterAdd("area", model.area);
                clsDbTool.ParameterAdd("buildarea", model.buildarea);

                //clsDbTool.SequenceText = "tbl_shopsproject";
                //clsDbTool.TableText = "tbl_shopsproject";
                clsDbTool.CommandText = strSQL;
                clsDbTool.Open();
                clsDbTool.BeginTransaction();
                clsDbTool.ExecuteScalar();

                clsDbTool.Commit();

            }
            catch (Exception ex)
            {
                clsDbTool.Rollback();
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }

            //return ret;
        }
        public void Update(CShopsBuild model)
        {
            string strSQL;


            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL =  "update tbl_shopsbuild set ";

                strSQL = strSQL + "projectid = @projectid,";
                strSQL = strSQL + "buildname = @buildname,";
                strSQL = strSQL + "buildfuction = @buildfuction,";
                strSQL = strSQL + "alllayer = @alllayer,";
                strSQL = strSQL + "groudlayer = @groudlayer,";
                strSQL = strSQL + "startdate = @startdate,";
                strSQL = strSQL + "enddate = @enddate,";
                strSQL = strSQL + "located = @located,";
                strSQL = strSQL + "property = @property,";
                strSQL = strSQL + "area = @area,";
                strSQL = strSQL + "buildarea = @buildarea";

                strSQL = strSQL + " WHERE 1=1";
                strSQL = strSQL + " AND buildid=@buildid";


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("buildid", model.buildid);
                clsDbTool.ParameterAdd("projectid", model.projectid);
                clsDbTool.ParameterAdd("buildname", model.buildname);
                clsDbTool.ParameterAdd("buildfuction", model.buildfuction);
                clsDbTool.ParameterAdd("alllayer", model.alllayer);
                clsDbTool.ParameterAdd("groudlayer", model.groudlayer);
                clsDbTool.ParameterAdd("startdate", model.startdate);
                clsDbTool.ParameterAdd("enddate", model.enddate);
                clsDbTool.ParameterAdd("located", model.located);
                clsDbTool.ParameterAdd("property", model.property);
                clsDbTool.ParameterAdd("area", model.area);
                clsDbTool.ParameterAdd("buildarea", model.buildarea);

                clsDbTool.CommandText = strSQL;

                clsDbTool.Open();
                clsDbTool.BeginTransaction();

                clsDbTool.ExecuteScalar();

                clsDbTool.Commit();

            }
            catch (Exception ex)
            {
                clsDbTool.Rollback();
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
        }
        public DataSet GetListByUpdate(string strId)
        {
            string strSQL;

            DataSet retDS = new DataSet();

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " SELECT B.buildid,B.projectid,B.buildname,B.buildfuction,B.alllayer,B.groudlayer,B.startdate,B.enddate,B.located,B.property,B.area,B.buildarea,p.projectname ";
                strSQL = strSQL + " FROM tbl_ShopsBuild B,tbl_ShopsProject P ";
                strSQL = strSQL + " WHERE 1=1 ";
                strSQL = strSQL + " AND B.projectid=P.projectid ";

                if (!string.IsNullOrEmpty(strId))
                {
                    strSQL = strSQL + " AND buildid=@buildid ";
                }


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("buildid", strId);
                clsDbTool.CommandText = strSQL;
                clsDbTool.Open();

                retDS = clsDbTool.GetDataSet();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
            return retDS;
        }
        public DataSet GetAllByProjectt(string companyid, string projectid) 
        {
            string strSQL;

            DataSet retDS = new DataSet();

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " SELECT B.buildid,B.projectid,B.buildname,B.buildfuction,B.alllayer,B.groudlayer,B.startdate,B.enddate,B.located,B.property,B.area,B.buildarea,p.projectname ";
                strSQL = strSQL + " FROM tbl_ShopsBuild B,tbl_ShopsProject P ";
                strSQL = strSQL + " WHERE 1=1 ";
                strSQL = strSQL + " AND B.projectid=P.projectid ";
                strSQL = strSQL + " AND P.companyid=@companyid ";
                strSQL = strSQL + " AND P.projectid =@projectid ";



                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("companyid", companyid);
                clsDbTool.ParameterAdd("projectid", projectid);
                clsDbTool.CommandText = strSQL;
                clsDbTool.Open();

                retDS = clsDbTool.GetDataSet();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
            return retDS;
        }
    }
}
